package com.kamikaze;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

public class Shift {
	
	int shift_number;
	String user_id = "0001";
	int wt_number;
	String wt_name;
	Date shift_date;
	String date_begin = "2012-08-01";
	String date_end = "2012-09-30";
	java.sql.Date shift_date_b;
	java.sql.Date shift_date_e;
	String on_time;
	String off_time;
	ArrayList<Shift> table = new ArrayList<Shift>();
	
	String classname = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://j.snpy.org/j2?user=team2&password=kamikaze";
	String checksql = "select * " +
			"from shift, worktypes " +
			"where user_id = ? " +
			"and (shift_date between ? and ?) " +
			"and shift.wt_number = worktypes.wt_number " +
			"order by shift_date asc";
	
	public Shift() {}
	
	public Shift(String user_id, String date_begin, String date_end) {
		this.user_id = user_id;
		this.date_begin = date_begin;
		this.date_end = date_end;
	}
	
	public Shift(String wt_name, Date shift_date, String on_time, String off_time) {
		this.wt_name = wt_name;
		this.shift_date = shift_date;
		this.on_time = on_time;
		this.off_time = off_time;
	}

	public String getDate_begin() {
		return date_begin;
	}

	public void setDate_begin(String date_begin) {
		this.date_begin = date_begin;
	}

	public String getDate_end() {
		return date_end;
	}

	public void setDate_end(String date_end) {
		this.date_end = date_end;
	}

	public ArrayList<Shift> getTable() {
		table.clear();
		shift_date_b = java.sql.Date.valueOf(date_begin);
		shift_date_e = java.sql.Date.valueOf(date_end);
		try {
			Class.forName(classname);
			Connection conn = DriverManager.getConnection(url);
			PreparedStatement pstmt = conn.prepareStatement(checksql);
			pstmt.setString(1, user_id);
			pstmt.setDate(2, shift_date_b);
			pstmt.setDate(3, shift_date_e);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				wt_name = rs.getString("wt_name");
				shift_date = rs.getDate("shift_date");
				on_time = rs.getTime("on_time").toString();
				off_time = rs.getTime("off_time").toString();
				Shift t = new Shift(wt_name, shift_date, on_time, off_time);
				table.add(t);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return table;
	}

	public void setTable(ArrayList<Shift> table) {
		this.table = table;
	}

	public int getShift_number() {
		return shift_number;
	}

	public void setShift_number(int shift_number) {
		this.shift_number = shift_number;
	}

	public String getUser_id() {
		return user_id;
	}

	public void setUser_id(String user_id) {
		this.user_id = user_id;
	}

	public int getWt_number() {
		return wt_number;
	}

	public void setWt_number(int wt_number) {
		this.wt_number = wt_number;
	}

	public Date getShift_date() {
		return shift_date;
	}

	public void setShift_date(Date shift_date) {
		this.shift_date = shift_date;
	}

	public String getWt_name() {
		return wt_name;
	}

	public void setWt_name(String wt_name) {
		this.wt_name = wt_name;
	}

	public String getOn_time() {
		return on_time;
	}

	public void setOn_time(String on_time) {
		this.on_time = on_time;
	}

	public String getOff_time() {
		return off_time;
	}

	public void setOff_time(String off_time) {
		this.off_time = off_time;
	}
}